/*
 * Copyright 2010
 * Jairou Software Organization
 * All Rights Reserved.
 * 
 * This software is the proprietary 
 * information of Roger Ramia
 * and the Jairou Software Organization 
 * Use is subject to license terms.
 * 
 * For licensing information please visit
 * http://www.jairou.org/license
 * 
 * @author Roger Ramia
 */
package org.jairou.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import org.jairou.core.Objects;
import org.jairou.core.Strings;
import org.jairou.core.XML;


/**
 * Common utility providing core JDBC support
 * and uniform access to Relational Databases
 * <br/>
 * This class executes pre-configured
 * statements defined in xml (usually named db.xml).
 * <br/>
 * The 3 primary functions supported are:
 * <pre>
 * 	DB.list() - which executes a query and returns a collection of records
 * 	DB.read() - which executes an query and returns a single record
 * 	DB.write() - which executes an update and returns void
 * </pre>
 * Also supports obtaining pre-configured connections or connections 
 * via connection pool.  Multiple connections can be configured however 
 * (at the moment) DB transactions are only coordinated across a single 
 * connection. All exception handling and db resource clean up is handled 
 * for you. 
 * 
 * @author Roger Ramia
 *
 */
public class DB {
	
	// static constant used for setting and getting the
	// current connection from a given context object
	public static final String CONNECTION_KEY = "connection";

	/**
	 * Attempts to open a pre-configured database connection using the 
	 * id and properties defined in jairou.xml. 
	 * <br/>
	 * JDBC Connections or Pools can be configured using the
	 * <db-resource> tag and specifying the JDBC connection or JNDI 
	 * lookup properties.
	 * <br/>
	 * For example:
	 *  
	 * A JNDI Connection Pool is configured using:
	 * {@code
	 * 	<db-resource id="myPooledConnection" type="jndi" lookup="java:comp/env/jdbc/MyPoolName"/>	
	 * }
	 * A native JDBC connection is configured using the following:	
	 *  <pre>{@code 
	 *	<db-resource id="myNativeConnection" type="jdbc">
	 *		<driver>com.mysql.jdbc.Driver</driver> 
	 *		<url>jdbc:mysql://localhost:3306/my_dbname</url> 
	 *		<user>dbUserName</user> 
	 *		<password>dbPassword</password>
	 *	</db-resource>
	 * }</pre>
	 * Note: This example assumes a mysql DB connection. Please refer to your 
	 * JDBC driver documentation for specific driver and url configuration
	 * <br/>
	 * Once connected a reference to the active connection is set
	 * within the passed in context object.  It can be obtain again
	 * via the DB.getConnection() method passing in the same
	 * context object.
	 * <br/>
	 * If an active connection already exists within the current context
	 * for the specified ID nothing is done, thus repeated calls to connect()
	 * will have no effect
	 * 
	 * @param id - id of the db-resource 
	 * @throws Exception
	 */
	public static void connect(String id, Object ctx) 
	throws SQLException, NamingException, ClassNotFoundException {
		Connection conn = getConnection(ctx);
		if (conn == null || conn.isClosed()) {
			String type = XML.get("db-resource."+id+".type");
			if (type != null) {
				if (type.equals("jndi")) {
					String lookup = XML.get("db-resource."+id+".lookup"); 
					InitialContext context = new InitialContext();
					DataSource ds = (DataSource) context.lookup(lookup);
					conn = ds.getConnection();
				}
				else if (type.equals("jdbc")) {
					String driver = XML.get("db-resource."+id+".driver");
					String url = XML.get("db-resource."+id+".url");
					String user = XML.get("db-resource."+id+".user");
					String password = XML.get("db-resource."+id+".password");
					if (driver != null && url != null && 
						user != null && password != null) {
						Class.forName(driver);
						conn = DriverManager.getConnection(url, user, password);
					}
					else throw new SQLException("Unable to obtain all connection parameters for resource:"+id+"\n Please check db-resource xml to ensure proper configuration");
				}
				else throw new SQLException("Invalid type specified for db-resource: "+id);
				Objects.set(CONNECTION_KEY, conn, ctx);
			}
			else throw new SQLException ("No type defined for db-resource:"+id+"\nValid database connection types include 'jndi' or 'jdbc'"); 
		}
		if (conn == null) throw new SQLException("unable to create connection for db-resource: "+id);
	}

	/**
	 * closes the connection
	 * associated with the current context
	 * @param ctx - the current context
	 * @throws Exception
	 */
	public static void close(Object ctx) 
	throws Exception {
		try {			
			if (connected(ctx)) {
				if (!getConnection(ctx).getAutoCommit()) 
					getConnection(ctx).commit();
			}
		}
		finally {
			if (connected(ctx)) {
				getConnection(ctx).close();				
			}
			Objects.set(CONNECTION_KEY, null, ctx);
		}		
	}
	
	/**
	 * rolls back all transactions (since the last commit)
	 * on the connection associated with the current context
	 * 
	 * @param ctx - current context used to share data across multiple
	 * 				db requests
	 * @throws Exception
	 */
	public static void rollback(Object ctx) 
	throws SQLException {
		Connection conn = getConnection(ctx);
		if (conn != null && !conn.isClosed()) {
			if (!conn.getAutoCommit()) conn.rollback();
		}
	}

	/**
	 * executes an XML defined query
	 * returning a single record from the database
	 * if more than one record is found only the first
	 * record of the result set is returned
	 * <br/>
	 * A read request will return a single object, by default
	 * the returned object class is a Map with the keys set to 
	 * the column names and values set to the column values.  
	 * <br/>
	 * The return type can changed to a custom POJO class, by simply specifying a 
	 * 'class' property in the statement's xml configuration.  
	 * For example:	
	 * XML configuration of...
	 *  <pre>{@code
	 * 	<statement id="getPerson" resource="myConnection" class="my.package.Person">
	 *		<sql>
	 *			select first_name, last_name
	 *			from person
	 *			where person_id = ?
	 *	 	</sql>
	 *		<input id="personId" type="integer"/>
	 *		<column id="first_name" alias="firstName"/>
	 *		<column id="last_name" alias="lastName"/>
	 *	</statement>
	 * }</pre>
	 * 
	 * A call to DB.read('getPerson', 43) would return an instance of the Person class
	 * with the firstName and lastName properties set to the values returned from the query.
	 * The record returned would be the person who's id is 43.  
	 * <br/>
	 * The only assumption made is that Person object's properties match the column alias defined above and 
	 * thus have public setFirstName() and setLastName() methods defined which accept the returned data type.
	 * the method names are determined by the "alias" attribute of the defined column element.
	 * <br/>
	 * If the class parameter specified is a native java class 
	 * (i.e. primitive wrapper or String class), the first column of the 
	 * result is loaded into the specified type and that value is returned.  
	 * For example: 
	 * XML configuration of...
	 * <pre>{@code
	 * <statement id="getMaxPersonId" class="java.lang.Integer">
	 * 		<sql>select max(id) from person</sql>
	 * </statement>
	 * }</pre>
	 * 
	 * A call to DB.read('getMaxPersonId') would return a single Integer set to the max 
	 * id value of the person table.
	 * <br/>
	 * Note: use DB.list() method to return more than one record
	 *  
	 * @param id - xml defined ID of the statement to run
	 * @param inputs - inputs used for specified parameters
	 * @param ctx - the current transaction context
	 * @return the resulting object read from the database
	 */
	public static Object read(String id, Object inputs, Object ctx)
	throws Exception {
		Object out = null;
		Collection list = list(id, inputs, ctx);
		if (list != null && list.size() > 0) out = list.iterator().next();
		return out;
	}
	
	/**
	 * overloaded read for queries without a context
	 * @param id
	 * @param inputs
	 * @return
	 * @throws Exception
	 */
	public static Object read(String id, Object inputs) 
	throws Exception {
		return read(id, inputs, null);
	}
	
	/**
	 * overloaded read for queries without input parameters
	 * @param id
	 * @return
	 * @throws Exception
	 */
	public static Object read(String id) 
	throws Exception {
		return read(id, null);
	}
	
	/**
	 * executes an XML defined query
	 * returning a all records from the result set
	 * as a Collection
	 * <br/>
	 * A list request will return a collection of objects, by default
	 * each object in the collection is a Map with the keys set to 
	 * the column names(or specified alias) and values set to the column values.  
	 * <br/>
	 * The object type can changed to a custom POJO class, by simply specifying a 
	 * 'class' property in the statement's xml configuration.  
	 * For example:	
	 * XML configuration of...
	 *  <pre>{@code
	 *  <statement id="getPeople" resource="myConnection" class="my.package.Person">
	 *		<sql>
	 *			select first_name, last_name
	 *			from person
	 *	 	</sql>
	 *		<column id="first_name" alias="firstName"/>
	 *		<column id="last_name" alias="lastName"/>
	 *	</statement>
	 * }</pre>
	 *
	 * A call to DB.list('getPeople') would return a collection of the Person objects
	 * with the properties set to the values returned from the database provided
	 * the class property names/types match the column(or alias) names and types.
	 * <br/>
	 * If the class parameter specified is a native java class 
	 * (i.e. primitive wrapper or String class), the first column of the 
	 * request is loaded into the specified type and that value is placed into the collection.  
	 * For example: 
	 * XML configuration of...
	 *  <pre>{@code
	 * <statement id="getPersonIds" resource="myConnection" class="java.lang.Integer">
	 * 		<sql>select id from person</sql>
	 * </statement> 
	 *  }</pre>
	 * A call to DB.list('getPersonIds') would return a collection of Integers set
	 * to each id in the person table.
	 * <br/>
	 * Note: use DB.read() method to return a single record
	 *  
	 * @param id - xml defined ID of the statement to run
	 * @param inputs - inputs used for specified parameters
	 * @param ctx - the current transaction context
	 * @return the resulting object read from the database
	 *   
	 * @param className
	 * @param methodName
	 * @param inputs
	 * @return
	 */
	public static Collection list(String id, Object inputs, Object ctx)
	throws Exception {
		Collection list = null;
		ResultSet rs = null;
		PreparedStatement stmt = null;
		boolean closeConnection = false;
		if (ctx == null) {
			if (inputs instanceof Map) ctx = inputs;
			else ctx = new HashMap();
			closeConnection = true;
		}
		try {
			if (id != null) {	
				String conn = XML.get("statement."+id+".resource");				
				if (conn != null) connect(conn, ctx);	
				else throw new Exception ("no db resource defined for statement "+id);
				stmt = prepare(id, inputs, ctx);				
				if (stmt != null) {
					stmt.execute();
					rs = stmt.getResultSet();
					if (rs != null)	{
						list = new ArrayList<Object>();
						while (rs.next())list.add(rowToObj(id, rs));
					}
				}
			}
		}
		catch(Throwable t) {
			rollback(ctx);
			throw new Exception(t);
		}
		finally {
			if (rs != null) rs.close();
			if (stmt != null) stmt.close();
			if (closeConnection) close(ctx);
		}
		if (list != null && !list.isEmpty()) return list;
		else return null;
	}
	
	/**
	 * overloaded list method for queries without a context
	 * 
	 * @param id
	 * @param inputs
	 * @return
	 * @throws Exception
	 */
	public static Collection list(String id, Object inputs) 
	throws Exception {
		Collection result = list(id, inputs, null);
		return result;
	}
	
	/**
	 * overloaded list method for queries without input parameters
	 * 
	 * @param id
	 * @return
	 * @throws Exception
	 */
	public static Collection list(String id) 
	throws Exception {
		return list(id, null);
	}
	

	/**
	 * Executes an XML defined update setting all 
	 * xml defined input parameters to the value 
	 * of the matching property on the specified input object.
	 * <br/>
	 * For example:	
	 * XML configuration of...
	 * <pre>{@code
	 * <statement id="updatePerson">
	 * 		<sql>update person set first_name = ?, last_name = ? where id = ?</sql>
	 * 		<input id="firstName" type="string"/>
	 * 		<input id="lastName" type="string"/>
	 * 		<input id="id" type="numeric"/>
	 * </statement> 
	 * }</pre>
	 * 
	 * A call to DB.update('updatePerson', inputs) would set the three inputs on the prepared
	 * statement (handling all standard conversions) to the values specified in the 'inputs' 
	 * object.  The inputs object is typically a Map with the keys matching the input ids
	 * and the values representing the values to be set on the statement.  The input object
	 * can also be a custom POJO provided the property names match the defined input ids.
	 * If there is only one input parameter the 'inputs' object can simply be a native 
	 * (primitive wrapper or String).
	 * <br/>
	 * If the class parameter specified is a native java class 
	 * (i.e. primitive wrapper or String class), the first column of the 
	 * request is loaded into the specified type and that value is placed into the collection.  
	 * For example: 
	 * XML configuration of...
	 * <pre>{@code
	 * <statement id="getPersonIds" class="java.lang.Integer">
	 * 		<sql>select id from person</sql>
	 * </statement> 
	 * }</pre>
	 * A call to DB.list('getPersonIds') would return a collection of Integers set
	 * to each id in the person table.
	 * <br/>
	 * Note: use DB.read() method to return a single record
	 *  
	 * @param id - xml defined ID of the statement to run
	 * @param inputs - inputs used for specified parameters
	 * @param ctx - the current transaction context
	 * @throws Exception - Any SQL exceptions along the way
	 */
	public static void update(String id, Object inputs, Object ctx)
	throws Exception {
		ResultSet rs = null;
		PreparedStatement stmt = null;
		boolean closeConnection = false;
		if (ctx == null) {
			if (inputs instanceof Map) ctx = inputs;
			else ctx = new HashMap();
			closeConnection = true;
		}
		try {
			if (id != null) {	
				String conn = XML.get("statement."+id+".resource");				
				if (conn != null) connect(conn, ctx);	
				else throw new Exception ("no db resource defined for statement "+id);				
				stmt = prepare(id, inputs, ctx);				
				if (stmt != null) stmt.execute();
			}
		}
		catch(Throwable t) {
			rollback(ctx);
			throw new Exception(t);
		}
		finally {
			if (rs != null) rs.close();
			if (stmt != null) stmt.close();
			if (closeConnection) close(ctx);
		}
	}
	
	public static void update(String id, Object inputs) 
	throws Exception {
		update(id, inputs, null);
	}
	
	public static void update(String id) 
	throws Exception {
		update(id, null);
	}
	
	/**
	 * Internal private method which creates a prepared statement given the id 
	 * of the xml statement element to prepare.  Assumes all input parameter 
	 * values exist in the specified inputs object.  
	 * <br/>
	 * For example an XML configuration of:
	 * <pre>{@code
	 * <statement id="example">
	 * 	<sql>select * from person where id = ?</sql>
	 * 	<input id="personId" type="integer"/>
	 * </statement>
	 * }</pre>
	 * Would assume the provided inputs object contains a 'personId' property.
	 * <br/>
	 * The inputs object is typically a Map with the keys matching the input ids defined
	 * in the xml statement element. The values of the Map are thus set on the statement.  
	 * However, the input object can also be a custom POJO provided the property names match 
	 * the xml defined input ids.  If there is only one input parameter the 'inputs' object 
	 * can simply be a native (primitive wrapper or String).
	 * <br/>
	 * If the parameter value does not exist within 
	 * the current Context attempts to set the value to null
	 * <br/>
	 * Also supports a <dynamic-where> configuration whereby if the 
	 * specified input has a value, the specified sql is appended to
	 * the existing where clause.  For example:
	 * <pre>{@code
	 * <statement>
	 * 	<sql>select * from person</sql>
	 * 	<dynamic-where>
	 * 		<input id="firstName" type="string" sql="first_name like ?"/>
	 * 		<input id="lastName" type="string" sql="last_name like ?"/>
	 *	</dynamic-where>
	 * <statement>
	 * }</pre>
	 * 		
	 * @param id
	 * @return
	 * @throws Exception
	 */	
	private static PreparedStatement prepare(String id, Object inputs, Object ctx) 
	throws Exception {
		PreparedStatement stmt = null;
		String sql = XML.get("statement."+id+".sql");
		String[] params = XML.getIds("statement."+id+".input");		
		if (sql != null) {
			// build dynamic where 
			String[] whereParams = XML.getIds("statement."+id+".dynamic-where.input");
			if (whereParams != null) {
				List<String> paramsList = new ArrayList<String>();
				paramsList.addAll(Arrays.asList(params));
				for (int c=0; c<whereParams.length; c++) {
					String whereParam = whereParams[c];
					if (Objects.get(whereParam, inputs) != null) {
						String condition = XML.get("statement."+id+".dynamic-where.input."+whereParam+".sql");
						if (!Strings.nullOrEmpty(condition)) {
							paramsList.add(whereParam);
							sql += sql.toLowerCase().contains("where ") ? " and " : " where ";
							sql += condition;
						}
					}
				}
				params = paramsList.toArray(params);
			}		
			
			// get connection and create statement
			Connection conn = getConnection(ctx);
			if (sql.indexOf("call ") != -1) stmt = conn.prepareCall(sql);
			else stmt = conn.prepareStatement(sql);
		}
		
		if (params != null) {
			for (int i=0; i<params.length; i++) {				
				
				// obtain value
				Object val = null;
				String alias = XML.get("statement."+id+".input."+params[i]+".alias");
				if (!Objects.isNative(inputs)) {
					if (alias != null) val = Objects.find(alias, inputs);
					else val = Objects.find(params[i], inputs);
				}
				else val = inputs;
	
				// identify param type
				String type = XML.get("statement."+id+".input."+params[i]+".type");
				if (Strings.nullOrEmpty(type)) type = XML.get("statement."+id+".dynamic-where.input."+params[i]+".type");
				String paramClass = XML.get("db-type."+type+".class");
				int jdbcType = Integer.parseInt(XML.get("db-type."+type));
				
				// set the statement param to the value
				if (val != null) {
					if (val instanceof String && Strings.nullOrEmpty((String)val)) stmt.setNull(i+1, jdbcType);
					else{
						val = Objects.load(paramClass, val);
						stmt.setObject(i+1, val, jdbcType);
					}					
				}
				else stmt.setNull(i+1, jdbcType);
			}
		}
		return stmt;
	}
	
	/**
	 * Transforms a given row into an object of the
	 * class defined by the specified statement id
	 * if no class is specified transforms the row
	 * into a HashMap of name value pairs.
	 * <br/>
	 * If a Native object is specified transforms the row
	 * into the the native object using the data from 
	 * the first column retrieved.
	 * 
	 * @param id
	 * @param rs
	 * @return
	 * @throws Exception
	 */
	private static Object rowToObj(String id, ResultSet rs) 
	throws Exception {
		Object out = null;
		if (id != null && rs != null) {
			String className = XML.get("statement."+id+".class");
			if (className == null) className = "java.util.HashMap";
			Class cls = Objects.toClass(className);
			if (Objects.isNativeClass(cls)) {
				out = Objects.load(className, rs.getObject(1));
			}
			else {
				out = cls.newInstance();
				ResultSetMetaData rsmd = rs.getMetaData();
				for(int c=1; c<=rsmd.getColumnCount(); c++) {
					String column = rsmd.getColumnLabel(c).toLowerCase();
					String alias = XML.get("statement."+id+".column."+column+".alias");
					if (alias == null) alias = column;
					String type = XML.get("statement."+id+".column."+column+".type");
					String colClassName = XML.get("db-type."+type+".class");
					Object val = rs.getObject(c);
					if (colClassName != null && type != null) val = Objects.load(colClassName, val);
					Objects.set(alias, val, out);
				}
			}
		}
		return out;
	}
	
	
	/**
	 * returns the current connection assigned to this context
	 * 
	 * @param ctx - current context
	 * @return active connection or null
	 */
	public static Connection getConnection(Object ctx) {
		Connection conn = null;
		if (ctx != null) {
			conn = (Connection) Objects.find(CONNECTION_KEY, ctx);
		}
		return conn;
	}
	
	
	/**
	 * indicates if the current connection exists and still active
	 * 
	 * @param ctx - current context
	 * @return boolean - true if exists and active
	 * @throws Exception SQL exceptions
	 */
	public static boolean connected(Object ctx) 
	throws Exception {
		return (getConnection(ctx) != null && !getConnection(ctx).isClosed());
	}
	
	
	/**
	 * commits all pending transactions on the current connection
	 * 
	 * @param ctx - current context
	 * @throws Exception - SQL exceptions
	 */
	public static void commit(Object ctx) 
	throws Exception {
		if (connected(ctx)) {
			getConnection(ctx).commit();
		}
	}
}